<?php 
/**
 * NOTICE OF LICENSE
 *
 * This source file is subject to the  Academic Free License (AFL 3.0)
 * that is available through the world-wide-web at this URL:
 * http://opensource.org/licenses/afl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to support@zprofess.com so we can send you a copy immediately.
 *
 * @category   	Prestashop module
 * @package		Help Desk
 * @version 	1.0
 * @author 		Rukan Hitokiri <rukanhitokiri@zprofess.com>
 * @copyright   Copyright (c) 2012 ZPROFESS. (http://zprofess.com)
 * @license    	http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
 */

class ZdepartmentObject extends ObjectModel
{
	/** @var string SQL Table name */
	protected 	$table ='zhelpdesk_department';
	/** @var string SQL Table identifier */
	protected 	$identifier = 'id_zhelpdesk_department';
	
	/** @var integer Object id */
    public      $id_zhelpdesk_department;  
    /** @var integer Object name */   
	public      $name;
	/** @var integer Object description */
	public      $description;
	/** @var integer Object active */
	public      $active = true;
	/** @var integer Object requice login */
	public 		$require_login = true;
	/** @var integer Object id mode */
	public      $id_moderator;
	/** @var datetime Object id date time add */
	public      $date_add;
	/** @var datetime Object id date time update */
	public      $date_upd;
	/** @var array Fields validity functions for admin panel forms */
	protected $fieldsValidate = array(
		'id_zhelpdesk_department' 	 => 'isInt',
		'active' 				 	 => 'isInt',
		'require_login'		 		 => 'isInt',

		
	);
	/** @var array Multilingual required fields for admin panel forms */
	protected $fieldsRequiredLang = array('name', 'description');
	/** @var array Multilingual fields validity functions for admin panel forms */
	protected $fieldsValidateLang = array(
			    'name' => 'isString',
			    'description' => 'isString'
				);
	/**
	 * Build object
	 *
	 * @param integer $id Existing object id in order to load object (optional)
	 * @param integer $id_lang Required if object is multilingual (optional)
	 */
	public function  __construct($id = null, $lang = null)
	{
		parent::__construct($id, $lang);
	}
	
	public function getTranslationsFieldsChild()
	{
		parent::validateFieldsLang();
		return parent::getTranslationsFields(array('name', 'description'));
	}

	/**
	 * @see ObjectModelCore::getFields()
	 * 
	 * Prepare fields for ObjectModel class (add, update)
	 * All fields are verified (pSQL, intval...)
	 *
	 * @return array All object fields
	 */
	public function getFields()
	{
		parent::validateFields();
		$fields['id_zhelpdesk_department'] = (int)($this->id_zhelpdesk_department);
		$fields['active'] = (int)($this->active);
		$fields['require_login'] = (int)($this->require_login);
		$fields['id_moderator'] = (int)($this->id_moderator);
		$fields['date_add'] = pSQL($this->date_add);
		$fields['date_upd'] = pSQL($this->date_upd);
		return $fields;
	}
	/**
	 * 
	 * @see ObjectModelCore::makeTranslationFields
	 * @param  $fields
	 * @param  $fieldsArray
	 * @param  $id_language
	 */
	protected function makeTranslationFields(&$fields, &$fieldsArray, $id_language)
	{
		$fields[$id_language]['id_lang'] = $id_language;
		$fields[$id_language][$this->identifier] = (int)($this->id);
		foreach ($fieldsArray as $field)
		{
			/* Check fields validity */
			if (!Validate::isTableOrIdentifier($field))
				die(Tools::displayError());

			/* Copy the field, or the default language field if it's both required and empty */
			if ((!$this->id_lang AND isset($this->{$field}[$id_language]) AND !empty($this->{$field}[$id_language])) 
			OR ($this->id_lang AND isset($this->$field) AND !empty($this->$field)))
				$fields[$id_language][$field] = $this->id_lang ? pSQL($this->$field) : pSQL($this->{$field}[$id_language], true);
			elseif (in_array($field, $this->fieldsRequiredLang))
				$fields[$id_language][$field] = $this->id_lang ? pSQL($this->$field) : pSQL($this->{$field}[Configuration::get('PS_LANG_DEFAULT')], true);
			else
				$fields[$id_language][$field] = '';
		}
	}
	/**
	 * Toggle object RequireLogin in database
	 *
	 * return boolean Update result
	 */
	public function toggleRequireLogin()
	{
		return Db::getInstance()->Execute('
				UPDATE `'.pSQL(_DB_PREFIX_.$this->table).'`
				SET `require_login` = !`require_login`
				WHERE `'.pSQL($this->identifier).'` = '.Tools::getValue($this->identifier));
	}
	
	/**
	 * 
	 * ADD or UPDATE staff to department
	 * @param objest $obj
	 * @param array $arrEmployees array id employee
	 */
	public function addEmployeeToStaff($obj, $arrEmployees)
	{
		if (!is_array($arrEmployees)) {
            $arrEmployees = array();
        }
        if ($obj instanceof ZDepartmentObject) {
            $id_department = $obj->id;
        } elseif (is_array($obj)) {
            $id_department = $obj['id_zhelpdesk_department'];
        } else {
            return;
        }
        
         $sql = '
	            SELECT `id_staff`
	            FROM `' . _DB_PREFIX_ . 'zhelpdesk_department_staff`';
        $arrStaff = DbCore::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
        $arrStaffTmp = array();
        foreach ($arrStaff AS $staff)
        	$arrStaffTmp[] =  $staff['id_staff'];
        
        if ($id_department){
        	foreach ($arrEmployees as $employee) {
            	if (!in_array($employee, $arrStaffTmp)){
            		// insert new staff by department
	                $row = array(
	                    'id_zhelpdesk_department' => (int) $id_department,
	                    'id_staff' => (int) ($employee));
	                Db::getInstance()->AutoExecute(_DB_PREFIX_ . 'zhelpdesk_department_staff', $row, 'INSERT');
            	}else {
            		// update department by staff
            		$row = array('id_zhelpdesk_department' => (int) $id_department);
	                $where = '`id_staff`='.(int)($employee);
	                Db::getInstance()->AutoExecute(_DB_PREFIX_ . 'zhelpdesk_department_staff', $row, 'UPDATE', $where);
            	}
        	}
        }
	}
	
	/**
	* Get id_employee, firtsname, lastname by id mode
	*
	* @param integer $id id mode
	* @return array 
	*/
	public static function _getModeById($id)
	{
		return Db::getInstance()->getRow('
		SELECT `id_employee`, `firstname`, `lastname` ,`email`
		FROM `'._DB_PREFIX_.'employee` 
		WHERE `id_employee` = '.(int)($id));
	}

	/**
	 * get all departmentt
	 * 	$id_lang lang default 
	 * @return array ('name'=>'name_department', 'value'=>'id_zhelpdesk_departmentd_')
	 */
	public static function getAllDepartMent ($id_lang,$field=false) {
		if (!$field)
			return Db::getInstance()->ExecuteS(' 
				SELECT zd.id_zhelpdesk_department as value,zdl.`name` as name FROM '._DB_PREFIX_.'zhelpdesk_department zd 
				LEFT JOIN '._DB_PREFIX_.'zhelpdesk_department_lang zdl ON (zd.id_zhelpdesk_department=zdl.id_zhelpdesk_department AND id_lang='.$id_lang.')'
			);
		else {
			$rs[0] = Db::getInstance()->ExecuteS(' 
				SELECT zd.id_zhelpdesk_department as value,zdl.`name` as name ,zdl.`description` as description 
				FROM '._DB_PREFIX_.'zhelpdesk_department zd 
				LEFT JOIN '._DB_PREFIX_.'zhelpdesk_department_lang zdl ON (zd.id_zhelpdesk_department=zdl.id_zhelpdesk_department AND id_lang='.$id_lang.')
				WHERE require_login=0 AND active=1
				'
			);
			$rs[1] = Db::getInstance()->ExecuteS(' 
				SELECT zd.id_zhelpdesk_department as value,zdl.`name` as name ,zdl.`description` as description 
				FROM '._DB_PREFIX_.'zhelpdesk_department zd 
				LEFT JOIN '._DB_PREFIX_.'zhelpdesk_department_lang zdl ON (zd.id_zhelpdesk_department=zdl.id_zhelpdesk_department AND id_lang='.$id_lang.')
				WHERE require_login=1 AND active=1
				'
			);  
			return $rs;
		}
	}
	/**
	 * 
	 * count staff by id department
	 * @param id department $id\
	 * 
	 * @return int
	 */
	public static function _countRowStaffById($id)
	{
		$result = Db::getInstance()->ExecuteS('
		SELECT `id_staff`
		FROM `'._DB_PREFIX_.'zhelpdesk_department_staff` 
		WHERE `id_zhelpdesk_department` = '.(int)($id));
		return count($result);
	}
	
	/**
	 * 
	 * Count status ticket by department
	 * @param integer $idDepartment id department
	 * @param integer $idStatus id status count
	 * 
	 * @return integer
	 */
	public static function countStatusByDepartment($idDepartment, $idStatus)
	{
		$aqlStatus = Db::getInstance()->getValue('
			SELECT SQL_CALC_FOUND_ROWS COUNT(`id_zhelpdesk_ticket`) as status_ticket 
			FROM `'._DB_PREFIX_.'zhelpdesk_ticket` a 
			WHERE a.`id_zhelpdesk_department` = '.$idDepartment.' 
				AND a.`id_zhelpdesk_ticket_status` = '.$idStatus.'
		');
		
		return $aqlStatus;
	}
	
}
?>